package xyz.ag.DAO.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import xyz.ag.Beans.Book;
import xyz.ag.DAO.BookDAOInterface;
import xyz.ag.Service.impl.BookServiceInterfaceImpl;
import xyz.ag.utils.c3p0utils;

public class BookDAOInterfaceImpl implements BookDAOInterface {

	
	
	@Override
	/**
	 * 获取所有分类表里面的图书分类信息 
	 * - 支持的部分 图书分类的加载
	 * 2019年12月7日08:01:16
	 * 基本实现
	 * 2019年12月7日09:31:12
	 */
	public HashMap<String, ArrayList<String>> getAllification() throws SQLException {
		HashMap<String, ArrayList<String>> map = new HashMap<String,ArrayList<String>>();
		Connection conn = c3p0utils.GetConnection();
		PreparedStatement ps = conn.prepareStatement("SELECT * FROM bookification");
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			String Firstificatio =rs.getString("Firstification");
			String Secondification= rs.getString("Secondification");
			if(map.get(Firstificatio)==null){
				ArrayList<String> list= new ArrayList<String>();
				list.add(Secondification);//居然有返回值
				map.put(Firstificatio,list);//第一个集合 补全元素
			}else{
				map.get(Firstificatio).add(Secondification);//已存在则直接添加
			}
		}
		c3p0utils.closeconn(rs, ps, conn);
		return map;
	}

	
	/**
	 * 查询推荐列表 从其中获取所有的推荐书籍的行数 这个行数将作为首页分页的依据以ajax的形式进行传递
	 * 2019年12月7日14:47:35
	 * @throws SQLException 
	 */
	@Override
	public Integer getrecommendedListNmuber() throws Exception  {
	
			Connection conn = c3p0utils.GetConnection();
			PreparedStatement ps = conn.prepareStatement("SELECT count(Bookid) numbers FROM recommended;");
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				return rs.getInt("numbers");
			}
			c3p0utils.closeconn(rs, ps, conn);
			return 0;
	}


	/**
	 * 推荐目录查询函数
	 * 这个方法查询推荐目录表 用来返回当前页的列表 实现分页功能 
	 * 回传的是一个book对象的list
	 * page 当前页数
	 * number 当前页显示多少行信息
	 * @throws SQLException 上报查询异常
	 */
	@Override
	public ArrayList<Book> getBookLimitpageList(int page, int number) throws SQLException {
		Connection conn = c3p0utils.GetConnection();
		
		ArrayList<Book> albook = new ArrayList<Book>();
		
		PreparedStatement ps = conn.prepareStatement("SELECT BookName,Bookauthot,Bookpress,imgsrc  FROM  recommended LIMIT ?,?");
		ps.setInt(1, page);
		ps.setInt(2,number);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			albook.add(new Book(rs.getString("BookName"),rs.getString("Bookauthot"),rs.getString("Bookpress"),rs.getString("imgsrc")));
		}
		c3p0utils.closeconn(rs, ps, conn);
		return albook;
	}


	/**
	 * 获得书的ID 用名字
	 * 基本是提供内部使用的方法 
	 * 数据聚合的基本方法之一
	 * 2019年12月7日20:24:04
	 * @throws SQLException 
	 */
	@Override
	public String getOneBookIDforName(String Book) throws SQLException {
Connection conn = c3p0utils.GetConnection();
		PreparedStatement ps = conn.prepareStatement("SELECT BookID  FROM  BOOK WHERE BookName=?");
		ps.setString(1, Book);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			return rs.getString("BookID");
		}
		c3p0utils.closeconn(rs, ps, conn);
		return "0";
	}
	
	/**
	 * 使用图书的id获得名字
	 * 基本是提供内部使用的方法 
	 * 2019年12月7日23:11:55
	 * @throws SQLException 
	 */
	@Override
	public String getOneBookNameforID(String BookID) throws SQLException {
Connection conn = c3p0utils.GetConnection();
		PreparedStatement ps = conn.prepareStatement("SELECT BookName  FROM  BOOK WHERE Bookid=?");
		ps.setString(1, BookID);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			String name = rs.getString("BookName");
			return name;
		}
		c3p0utils.closeconn(rs, ps, conn);
		return "null";
	}
	
	/**
	 * 使用图书的id获得价格
	 * 基本是提供内部使用的方法 
	 * 2019年12月7日23:11:55
	 * @throws SQLException 
	 */
	@Override
	public String getOneBookPriceforID(String BookID) throws SQLException {
		Connection conn = c3p0utils.GetConnection();
		PreparedStatement ps = conn.prepareStatement("SELECT Bookprice  FROM  BOOK WHERE Bookid=?");
		ps.setString(1, BookID);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			String Bookprice = rs.getString("Bookprice");
			return Bookprice;
		}
		c3p0utils.closeconn(rs, ps, conn);
		return "免费的";
	}


	

	@Override
	public ArrayList<Book> getBookListByCondition(String page, String number, String s) throws SQLException {
		Connection conn = c3p0utils.GetConnection();
		ArrayList<Book> albook = new ArrayList<Book>();
		PreparedStatement ps =null;
		System.out.println(s);
		if("ALL".equals(s)){
			System.out.println("all了");
			ps = conn.prepareStatement("SELECT BookName,Bookauthot,Bookpress,imgsrc  FROM  book LIMIT ?,?");
			ps.setInt(1, Integer.parseInt(page)-1);
			ps.setInt(2,Integer.parseInt(number));
		}else{
			Integer id = getbookificationIDByCondlition(s);
			ps = conn.prepareStatement("SELECT BookName,Bookauthot,Bookpress,imgsrc  FROM  book where category =? LIMIT ?,?");
			ps.setInt(1, id);
			ps.setInt(1, Integer.parseInt(page)-1);
			ps.setInt(2,Integer.parseInt(number));
		}
		
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			albook.add(new Book(rs.getString("BookName"),rs.getString("Bookauthot"),rs.getString("Bookpress"),rs.getString("imgsrc")));
		}
		c3p0utils.closeconn(rs, ps, conn);
		return albook;
	}
	
	
	
	/**
	 * 查询类别的id值 入参是前端给的一个xxx-xxx形式的字符串
	 * @param s
	 * @return
	 */
	public Integer getbookificationIDByCondlition(String s){
		Connection conn = c3p0utils.GetConnection();
		try {
			PreparedStatement ps = conn.prepareStatement("select id from bookification where Firstification = ? and Secondification = ?");
			ps.setString(1, s.split("-")[0]);
			ps.setString(2, s.split("-")[1]);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				Integer id = rs.getInt("id");
				return id;
			}
			c3p0utils.closeconn(rs, ps, conn);
		} catch (Exception e) {
			System.err.println("图书分类查询错误 ");
			e.printStackTrace();
			return 0;
		}
		return null;
		
		
		
	}


	/**
	 * 根据分类查询每个分类下的图书总数
	 * @throws SQLException 
	 */
	@Override
	public Integer initshowlistnumber(String s) throws SQLException {
		Connection conn = c3p0utils.GetConnection();
		PreparedStatement ps =null;
		
		System.out.println(s);
		if("ALL".equals(s)){
			ps = conn.prepareStatement("SELECT Count(BookName) number  FROM  book ");
		
		}else{
			Integer id = getbookificationIDByCondlition(s);
			ps = conn.prepareStatement("SELECT Count(BookName) number  FROM  book where category =? ");
			ps.setInt(1, id);
		}
		
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			String number = rs.getString("number");
			return Integer.parseInt(number);
		}
		c3p0utils.closeconn(rs, ps, conn);
		return 0;
	}


	/**
	 * 单本图书的展示页面 
	 * 用来展示单本图书 通过书名查询 返回一个图书对象的封装集合体
	 * @throws SQLException 
	 */
	@Override
	public Book shouBookByBookName(String bookname) throws SQLException {
		Connection conn = c3p0utils.GetConnection();
		PreparedStatement ps = conn.prepareStatement("SELECT BookName,BookID,Bookauthot,Bookpress,Bookprice,BookResidue,category,imgsrc FROM  BOOK WHERE BookName=?");
		ps.setString(1, bookname);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			Book book = new Book();
			book.setBookName(rs.getString("BookName"));
			book.setBookID(rs.getString("BookID"));
			book.setBookauthot(rs.getString("Bookauthot"));
			book.setBookpress(rs.getString("Bookpress"));
			book.setBookprice(rs.getDouble("Bookprice"));
			book.setBookResidue(rs.getInt("BookResidue"));
			book.setCategory(rs.getString("category"));
			book.setImgsrc(rs.getString("imgsrc"));
			return book;
		}
		c3p0utils.closeconn(rs, ps, conn);
		return null;
	}


	/**
	 * 数据聚合的支持方法 用来查询一本书的分类是什么
	 * @throws SQLException 
	 */
	@Override
	public String getBookcategoryByID(String categoryID) throws SQLException {
		Connection conn = c3p0utils.GetConnection();
		PreparedStatement ps = conn.prepareStatement("select Firstification,Secondification from bookification where id= ?");
		ps.setInt(1, Integer.parseInt(categoryID));
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			return rs.getString("Firstification")+"-"+rs.getString("Secondification");
		}
		c3p0utils.closeconn(rs, ps, conn);
		return null;
	}
}






